﻿using CodeGenerator.Ado;
using CodeGenerator.Dtos;
using System.Data;

namespace CodeGenerator.Facade
{
    public class ConnectionFacade
    {
        private readonly DBType type;
        private readonly string connectionStr;
        private readonly AdoProviderBase dbProvider;
        public ConnectionFacade(DBType type, string connectionStr)
        {
            this.type = type;
            this.connectionStr = connectionStr;

            var provider = AdoFactory.GetProvider(type, connectionStr);
            dbProvider = provider;
        }

        /// <summary>
        /// 连接/测试
        /// </summary>
        /// <param name="type"></param>
        /// <param name="connectionStr"></param>
        /// <returns></returns>
        public FuncResult Connection()
        {
            var isok = dbProvider.Connection();
            if (!isok)
            {
                return FuncResult.Fail("连接失败！");
            }
            return FuncResult.Success();
        }



        /// <summary>
        /// 获取表
        /// </summary>
        /// <param name="databaseName">数据库</param>
        /// <returns></returns>
        public FuncResult<List<ShowAllTableDTO>> GetTableList(string databaseName)
        {
            string sql = $"Select name from {databaseName}.sys.tables  \r\n   union\r\n   Select name from {databaseName}.sys.views";
            var tables = dbProvider.ExecuteQueryAsync(sql);
            var datas = DataTableToList(tables.Data);
            return datas;
        }

        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="databaseName"></param>
        /// <param name="tables"></param>
        /// <returns></returns>
        public FuncResult<List<TableInfoDTO>> GetTableInfo(string databaseName, List<string> tables)
        {
            var newTable = tables.Select(r => "'" + r + "'").ToList();
            string str = string.Join(',', newTable);

            string sql = @$"select d.TABLE_CATALOG,d.TABLE_NAME,d.COLUMN_NAME,d.ORDINAL_POSITION,d.IS_NULLABLE,d.DATA_TYPE,C.value COLUMN_DESCRIPTION
                            from {databaseName}.sys.tables a 
                            inner join {databaseName}.sys.columns b 
                            on b.object_id = a.object_id
                            left join {databaseName}.sys.extended_properties c 
                            on c.major_id = b.object_id and c.minor_id = b.column_id
                            left join information_schema.columns d 
                            on a.name = d.TABLE_NAME and b.name = d.COLUMN_NAME
                            where a.name in ({str}) and  d.TABLE_CATALOG = '{databaseName}'";

            var tablesInfos = dbProvider.ExecuteQueryAsync(sql);

            var list = OtherFacade.TableToListModel<TableInfoDTO>(tablesInfos.Data);

            return list;
        }



        //待优化
        private List<ShowAllTableDTO> DataTableToList(DataTable dt)
        {
            List<ShowAllTableDTO> dto = new List<ShowAllTableDTO>();
            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn columns in dt.Columns)
                {
                    dto.Add(new ShowAllTableDTO { TableName = row[columns.ColumnName].ToString() });
                }
            }

            return dto;
        }


    }
}
